// 数据库迁移执行脚本
const fs = require('fs')
const path = require('path')
const { Pool } = require('pg')

// 数据库连接配置
const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'aide',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'unidt@123'
})

const MIGRATIONS_DIR = path.join(__dirname, '../database/migrations')

async function createMigrationsTable() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      version TEXT PRIMARY KEY,
      description TEXT NOT NULL,
      executed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      execution_time_ms INTEGER,
      success BOOLEAN NOT NULL DEFAULT true
    )
  `)
}

async function getExecutedMigrations() {
  const result = await pool.query('SELECT version FROM schema_migrations WHERE success = true ORDER BY version')
  return result.rows.map(row => row.version)
}

async function getAllMigrations() {
  const files = fs.readdirSync(MIGRATIONS_DIR)
  return files
    .filter(file => file.endsWith('.sql'))
    .map(file => {
      const version = file.replace('.sql', '')
      return {
        version,
        file: path.join(MIGRATIONS_DIR, file)
      }
    })
    .sort((a, b) => a.version.localeCompare(b.version))
}

async function executeMigration(migration) {
  const startTime = Date.now()
  const sql = fs.readFileSync(migration.file, 'utf8')
  
  console.log(`🔄 执行迁移: ${migration.version}`)
  
  try {
    // 开始事务
    await pool.query('BEGIN')
    
    // 执行迁移SQL
    await pool.query(sql)
    
    // 记录迁移执行
    const executionTime = Date.now() - startTime
    await pool.query(`
      INSERT INTO schema_migrations (version, description, execution_time_ms, success)
      VALUES ($1, $2, $3, $4)
      ON CONFLICT (version) DO UPDATE SET
        executed_at = NOW(),
        execution_time_ms = $3,
        success = $4
    `, [migration.version, `Migration ${migration.version}`, executionTime, true])
    
    // 提交事务
    await pool.query('COMMIT')
    
    console.log(`✅ 迁移完成: ${migration.version} (${executionTime}ms)`)
    return true
    
  } catch (error) {
    // 回滚事务
    await pool.query('ROLLBACK')
    
    // 记录失败的迁移
    const executionTime = Date.now() - startTime
    try {
      await pool.query(`
        INSERT INTO schema_migrations (version, description, execution_time_ms, success)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (version) DO UPDATE SET
          executed_at = NOW(),
          execution_time_ms = $3,
          success = $4
      `, [migration.version, `Migration ${migration.version} FAILED`, executionTime, false])
    } catch (recordError) {
      console.error('记录失败迁移时出错:', recordError.message)
    }
    
    console.error(`❌ 迁移失败: ${migration.version}`)
    console.error(`错误: ${error.message}`)
    return false
  }
}

async function runMigrations(targetVersion = null) {
  try {
    console.log('🚀 开始数据库迁移...\n')
    
    // 确保迁移表存在
    await createMigrationsTable()
    
    // 获取已执行的迁移
    const executedMigrations = await getExecutedMigrations()
    console.log(`📋 已执行迁移数量: ${executedMigrations.length}`)
    
    // 获取所有迁移文件
    const allMigrations = await getAllMigrations()
    console.log(`📁 发现迁移文件数量: ${allMigrations.length}\n`)
    
    // 找出待执行的迁移
    const pendingMigrations = allMigrations.filter(migration => {
      const shouldExecute = !executedMigrations.includes(migration.version)
      const withinTarget = !targetVersion || migration.version <= targetVersion
      return shouldExecute && withinTarget
    })
    
    if (pendingMigrations.length === 0) {
      console.log('✅ 没有待执行的迁移')
      return true
    }
    
    console.log(`📋 待执行迁移 (${pendingMigrations.length}):`)
    pendingMigrations.forEach(migration => {
      console.log(`   - ${migration.version}`)
    })
    console.log('')
    
    // 执行迁移
    let successCount = 0
    for (const migration of pendingMigrations) {
      const success = await executeMigration(migration)
      if (success) {
        successCount++
      } else {
        console.log(`\n❌ 迁移在 ${migration.version} 处停止`)
        break
      }
    }
    
    console.log(`\n🎉 迁移完成! 成功执行 ${successCount}/${pendingMigrations.length} 个迁移`)
    return successCount === pendingMigrations.length
    
  } catch (error) {
    console.error('❌ 迁移过程出错:', error.message)
    return false
  } finally {
    await pool.end()
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  const targetVersion = process.argv[2]
  runMigrations(targetVersion)
    .then(success => {
      process.exit(success ? 0 : 1)
    })
    .catch(error => {
      console.error('Fatal error:', error)
      process.exit(1)
    })
}

module.exports = { runMigrations }
